IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cms_sp_MediaContentFindByKeywords]')
              AND TYPE IN (N'P' ,N'PC')
   )
    DROP PROCEDURE [dbo].[cms_sp_MediaContentFindByKeywords]
GO
CREATE PROCEDURE [dbo].[cms_sp_MediaContentFindByKeywords] 
(
    @SearchQuery                VARCHAR(MAX)
   ,@IsInAdministrationArea     BIT = NULL
   ,@SiteGroupId                INT = NULL
   ,@SiteId                     INT = NULL
   ,@MediaId                    INT = NULL
)
AS
	DECLARE @Searched TABLE 
	        (MediaId INT NOT NULL); 
	
	WITH tree 
	     AS (
	         SELECT MediaId
	               ,ParentMediaId
	         FROM   Media
	         WHERE  MediaId = @MediaId
	                OR (@MediaId IS NULL AND ParentMediaId IS NULL) 
	         UNION ALL 
	         --recursive member 
	         SELECT t.MediaId
	               ,t.ParentMediaId
	         FROM   Media t
	                JOIN tree AS a
	                     ON  t.ParentMediaId = a.MediaId
	     )
	
	INSERT INTO @Searched
	  (
	    MediaId
	  )
	SELECT MediaId
	FROM   tree 
	
	SELECT M.[MediaContentId]      AS [Id]
	      ,M.[MediaId]
	      ,M.[SiteId]
	      ,M.[SiteGroupId]
	      ,M.[MetaCode]
	      ,M.[Description]
	      ,M.[CreatedBy]
	      ,M.[DateCreated]
	      ,M.[LastUpdatedBy]
	      ,M.[DateLastUpdated]
	      ,M.BlockFromSearchEngines
	      ,[MediaContentName]
	      ,MediaFiles.MediaFileId  AS MainFile_Id
	      ,IsPrimary               AS MainFile_IsPrimary
	      ,MimeTypes.MimeTypeId    AS MainFile_MimeTypeId
	      ,MimeTypes.MimeTypeId    AS MainFile_MimeType_Id
	      ,ContentType             AS MainFile_MimeType_ContentType
	      ,MimeTypeName            AS MainFile_MimeType_MimeTypeName
	      ,IsImage                 AS MainFile_MimeType_IsImage
	      ,LinkedFileName          AS MainFile_LinkedFileName
	      ,OriginalFileName        AS MainFile_OriginalFileName
	      ,FileSize                AS MainFile_FileSize
	      ,Height                  AS MainFile_Height
	      ,Width                   AS MainFile_Width
	FROM   [dbo].MediaContents M
	       JOIN @Searched s
	            ON  M.MediaId = S.MediaId
	       JOIN dbo.MediaContentFileLinks
	            ON  MediaContentFileLinks.MediaContentId = M.MediaContentId
	                AND IsPrimary = 1
	       JOIN dbo.MediaFiles
	            ON  MediaContentFileLinks.MediaFileId = MediaFiles.MediaFileId
	       LEFT JOIN dbo.MimeTypes
	            ON  MimeTypes.MimeTypeId = MediaFiles.MimeTypeId
	WHERE  (
	           (M.SiteGroupId IS NULL AND M.SiteId IS NULL)
	           OR (M.SiteGroupId = @SiteGroupId AND M.SiteId IS NULL)
	           OR (M.SiteGroupId = @SiteGroupId AND M.SiteId = @SiteId)
	       )
	       AND M.MediaContentName LIKE '%' + @searchQuery + '%'
GO

